package com.wangsir.dao;

import com.wangsir.entity.Admin;
import com.wangsir.entity.Student;
import com.wangsir.util.JdbcHelper;
import com.wangsir.util.PagerVO;
import com.wangsir.util.Result;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class StudentDao {


    //查询表中的学生的总数量
    public int count(String whereSql){
        if(whereSql == null){
            whereSql = "";
        }
        JdbcHelper jdbcHelper = new JdbcHelper();
        ResultSet resultSet = jdbcHelper.executeQuery("select count(1) from tb_student" + whereSql);
        try {
            //指针下移
            resultSet.next();
            //返回第一个索引列表
            return resultSet.getInt(1);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            jdbcHelper.closeDB();
        }
        return 0;
    }
    //重载上面的方法(无参数)
    public int count(){
        return count("");
    }


    //根据学生的学号进行查询
    public Student getBySno(String sno) {
        JdbcHelper jdbcHelper = new JdbcHelper();
        ResultSet resultSet = jdbcHelper.executeQuery("select * from tb_student where sno = ?",sno);
        try {
            if(resultSet.next()){
                //调用下面写的静态接口返回学生对象，增加代码的复用性
                return getStudent(resultSet);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            jdbcHelper.closeDB();
        }
        //如果上面sql没有查询到对象，就让他返回为null
        return null;
    }



    //返回前端告诉用户有多少条记录，和当前页面是第几页
    public PagerVO<Student> page(int currentPage,int pageSize,String whereSql){
        PagerVO<Student> studentPagerVO = new PagerVO<>();
        //设置当前第几页
        studentPagerVO.setCurrentPage(currentPage);
        //设置一页有多少学生
        studentPagerVO.setPageSize(pageSize);
        JdbcHelper jdbcHelper = new JdbcHelper();
        ResultSet resultSet = jdbcHelper.executeQuery("select count(1) from tb_student" + whereSql);
        try{
            resultSet.next();
            int total = resultSet.getInt(1);
            //设置总人数，数据库中的总行数
            studentPagerVO.setTotalSize(total);
            //select * from tb_student where ...limit 10 , 10
            resultSet = jdbcHelper.executeQuery("select * from tb_student" +
                    //limit后边一定是需要写一个空格的否则不符合语法
                    whereSql + "limit " + ((currentPage - 1) * pageSize)+ "," + pageSize);
            List<Student> list = new ArrayList<>();
            while(resultSet.next()){
                //调用下面写的静态接口返回学生对象，增加代码的复用性
                list.add(getStudent(resultSet));
            }
            studentPagerVO.setList(list);
            return studentPagerVO;
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            jdbcHelper.closeDB();
        }
        return null;
    }

    //删除学生
    public int deleteBySno(String sno){
        JdbcHelper jdbcHelper = new JdbcHelper();
        int row = jdbcHelper.executeUpdate("delete from tb_student where sno = ?", sno);
        jdbcHelper.closeDB();
        return row;
    }

    //增加学生
    public int insertStudent(Student student){
        JdbcHelper jdbcHelper = new JdbcHelper();
        int result = jdbcHelper.executeUpdate("insert into tb_student values(?,?,?,?,?,?,?,?,?)",
                student.getSno(), student.getPassword(), student.getName(),
                student.getTele(), student.getEnterDate(), student.getAge(),
                student.getGender(), student.getAddress(), student.getClazzNO());
        jdbcHelper.closeDB();
        return result;
    }

    //修改学生
    /*
    * student 里面有属性为null的话，就不更新这个属性，进行忽视，如果不为null的话进行更新
    * */
    public int update(Student student){
        JdbcHelper jdbcHelper = new JdbcHelper();
        int result = 0;
        String sql= "update tb_student set ";
        List<Object> params = new ArrayList<>();
        if(student.getPassword()!=null){
            sql += "password = ?,";
            params.add(student.getPassword());
        }
        if(student.getName()!=null){
            sql += "name = ?,";
            params.add(student.getName());
        }
        if(student.getEnterDate()!=null){
            sql += "enterdate = ?,";
            params.add(student.getEnterDate());
        }
        if(student.getAge()!=null){
            sql += "age = ?,";
            params.add(student.getAge());
        }
        if(student.getTele()!=null){
            sql += "tele = ?,";
            params.add(student.getTele());
        }
        if(student.getGender()!=null){
            sql += "gender = ?,";
            params.add(student.getGender());
        }
        if(student.getAddress()!=null){
            sql += "address = ?,";
            params.add(student.getAddress());
        }
        if(student.getClazzNO()!=null){
            sql += "clazzno = ?,";
            params.add(student.getClazzNO());
        }
        //拼接完字符串之后，后面会多出一个","因此要把最后一个逗号给去掉，采用截取的方法
        sql = sql.substring(0,sql.length() - 1);
        sql += " where sno = '" + student.getSno() + "'";
        //将list集合转换为数组，因为参数是数组
        result = jdbcHelper.executeUpdate(sql, params.toArray());
        jdbcHelper.closeDB();
        return result;
    }


    //共同代码抽出来作为一个方法
    public static Student getStudent(ResultSet resultSet) throws SQLException {
        Student student = new Student();
        student.setSno(resultSet.getString("sno"));
        student.setPassword(resultSet.getString("password"));
        student.setName(resultSet.getString("name"));
        student.setTele(resultSet.getString("tele"));
        student.setEnterDate(resultSet.getDate("enterdate"));
        student.setAge(resultSet.getInt("age"));
        student.setGender(resultSet.getString("gender"));
        student.setAddress(resultSet.getString("address"));
        student.setClazzNO(resultSet.getString("clazzno"));
        return student;
    }


}
